package com.zretc.device.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.zretc.dbtools.DBTools;
import com.zretc.device.entity.Device_In;

public class Device_InDao {
	/**
	 * 增加入库的方法
	 * 
	 * @return
	 */
	public boolean addDevIn(Device_In devin) {
		String sql = "insert into device_in values (?,?,?,?,?,?,?,?)";
		int n = DBTools.getUpdate(sql, devin.getIn_id(), devin.getDevice_code(), devin.getIn_date(),
				devin.getProvider(), devin.getProvider_tele(), devin.getIn_num(), devin.getPrice(), devin.getBuyer());
		if (n == 1) {
			String updateSql = "UPDATE device set device.now_num =device.now_num+(select device_in.in_num from device_in where device_in.in_id=?),device.total_num =device.total_num+(select device_in.in_num from device_in where device_in.in_id=?) where device.device_code  = ?";
			DBTools.getUpdate(updateSql, devin.getIn_id(), devin.getIn_id(), devin.getDevice_code());
		}
		return n > 0;
	}

	/**
	 * 查询所有
	 * 
	 * @return
	 */
	public List<Device_In> findDevInAll() {
		// 创建集合
		List<Device_In> list = new ArrayList<Device_In>();
		String sql = "select * from device_in";
		ResultSet rs = DBTools.getQuery(sql, null);
		try {
			while (rs.next()) {
				// 入库编号
				int in_id = rs.getInt(1);
				// 设备编号
				String device_code = rs.getString(2);
				// 入库日期
				String in_date = rs.getString(3);
				// 供应商
				String provider = rs.getString(4);
				// 供应商电话
				String provider_tele = rs.getString(5);
				// 入库数量
				int in_num = rs.getInt(6);
				// 单价
				double price = rs.getDouble(7);
				// 采购者
				String buyer = rs.getString(8);
				// 创建对象
				Device_In di = new Device_In(in_id, device_code, in_date, provider, provider_tele, in_num, price,
						buyer);
				list.add(di);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 释放资源
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 根据设备编号模糊查询
	 * 
	 * @return
	 */
	public List<Device_In> findDevInByCode(String devCode) {
		// 创建集合
		List<Device_In> list = new ArrayList<Device_In>();
		String sql = "select * from device_in where device_code like ?";
		ResultSet rs = DBTools.getQuery(sql, "%" + devCode + "%");
		try {
			while (rs.next()) {
				// 入库编号
				int in_id = rs.getInt(1);
				// 设备编号
				String device_code = rs.getString(2);
				// 入库日期
				String in_date = rs.getString(3);
				// 供应商
				String provider = rs.getString(4);
				// 供应商电话
				String provider_tele = rs.getString(5);
				// 入库数量
				int in_num = rs.getInt(6);
				// 单价
				double price = rs.getDouble(7);
				// 采购者
				String buyer = rs.getString(8);
				// 创建对象
				Device_In di = new Device_In(in_id, device_code, in_date, provider, provider_tele, in_num, price,
						buyer);
				list.add(di);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 释放资源
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 根据入库日期模糊查询
	 * 
	 * @return
	 */
	public List<Device_In> findDevInByDate(String inDate) {
		// 创建集合
		List<Device_In> list = new ArrayList<Device_In>();
		String sql = "select * from device_in where in_date like ?";
		ResultSet rs = DBTools.getQuery(sql, "%" + inDate + "%");
		try {
			while (rs.next()) {
				// 入库编号
				int in_id = rs.getInt(1);
				// 设备编号
				String device_code = rs.getString(2);
				// 入库日期
				String in_date = rs.getString(3);
				// 供应商
				String provider = rs.getString(4);
				// 供应商电话
				String provider_tele = rs.getString(5);
				// 入库数量
				int in_num = rs.getInt(6);
				// 单价
				double price = rs.getDouble(7);
				// 采购者
				String buyer = rs.getString(8);
				// 创建对象
				Device_In di = new Device_In(in_id, device_code, in_date, provider, provider_tele, in_num, price,
						buyer);
				list.add(di);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 释放资源
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 修改
	 * 
	 * @return
	 */
	public boolean modDevIn(Device_In devin) {
		// 查找原本的值
		Device_In di = findDevInById(devin.getIn_id());
		// 调用工具
		String sql = "update device_in set device_code=?,in_date=?,provider=?,provider_tele=?,in_num=?,price=?,buyer=? where in_id=?";
		int n = DBTools.getUpdate(sql, devin.getDevice_code(), devin.getIn_date(), devin.getProvider(),
				devin.getProvider_tele(), devin.getIn_num(), devin.getPrice(), devin.getBuyer(), devin.getIn_id());
		if (n == 1) {
			// 更新库存表
			String updateSql = "UPDATE device set device.now_num =device.now_num-?+(select device_in.in_num from device_in where device_in.in_id=?),device.total_num =device.total_num-?+(select device_in.in_num from device_in where device_in.in_id=?) where device.device_code  = ?";
			DBTools.getUpdate(updateSql, di.getIn_num(), devin.getIn_id(), di.getIn_num(), devin.getIn_id(),
					devin.getDevice_code());
		}
		return n > 0;
	}

	/**
	 * 根据单号查询
	 * 
	 * @return
	 */
	public Device_In findDevInById(int inId) {
		// 创建集合
		Device_In din = null;
		String sql = "select * from device_in where in_id=?";
		ResultSet rs = DBTools.getQuery(sql, inId);
		try {
			if (rs.next()) {
				// 入库编号
				int in_id = rs.getInt(1);
				// 设备编号
				String device_code = rs.getString(2);
				// 入库日期
				String in_date = rs.getString(3);
				// 供应商
				String provider = rs.getString(4);
				// 供应商电话
				String provider_tele = rs.getString(5);
				// 入库数量
				int in_num = rs.getInt(6);
				// 单价
				double price = rs.getDouble(7);
				// 采购者
				String buyer = rs.getString(8);
				// 创建对象
				din = new Device_In(in_id, device_code, in_date, provider, provider_tele, in_num, price, buyer);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 释放资源
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return din;
	}

	/**
	 * 删除入库信息
	 * 
	 * @return
	 */
	public boolean delDevIn(int in_id) {
		// 查找原本的值
		Device_In di = findDevInById(in_id);
		String sql = "delete from device_in where in_id=?";
		int n = DBTools.getUpdate(sql, in_id);

		if (n == 1) {
			// 更新库存表
			String updateSql = "UPDATE device set device.now_num =device.now_num-?,device.total_num =device.total_num-? where device.device_code  = ?";
			DBTools.getUpdate(updateSql,di.getIn_num(),di.getIn_num(),di.getDevice_code());
		}
		return n > 0;
	}

}
